﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Marbles.Data
{
    public class SqlDataSourceProvider : BaseDataSource, IDataSource
    {
        // Fields
        private SqlConnection _dbc = null;

        // Methods
        public SqlDataSourceProvider(string ConnectionString)
        {
            this._dbc = new SqlConnection(ConnectionString);
        }

        public void Close()
        {
            if (this._dbc.State != ConnectionState.Closed)
            {
                this._dbc.Close();
            }
        }

        private SqlCommand ConvertToSQLCommand(DataSourceCommand command)
        {
            string query = command.CommandText;
            SqlCommand cmd = new SqlCommand(query, this._dbc);
            foreach (string s in command.Parameters.Keys)
            {
                if (command.Parameters[s].GetType() == typeof(byte[]))
                {
                    SqlParameter parm = new SqlParameter(s, SqlDbType.Image);
                    parm.Value = command.Parameters[s];
                    cmd.Parameters.Add(parm);
                }
                else
                {
                    if (command.Parameters[s] == null)
                    {
                        cmd.Parameters.Add(new SqlParameter(s, DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter(s, command.Parameters[s]));
                    }
                }
            }
            return cmd;
        }

        public int ExecuteCommand(DataSourceCommand Command)
        {
            SqlCommand cmd = this.ConvertToSQLCommand(Command);
            if (!this.IsConnected())
            {
                this.Open();
            }
            int value = cmd.ExecuteNonQuery();
            this.Close();
            return value;
        }

        public DataSet FetchDataSet(DataSourceCommand Command)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(this.ConvertToSQLCommand(Command));
            if (!this.IsConnected())
            {
                this.Open();
            }
            da.Fill(ds);
            this.Close();
            return ds;
        }

        public DataTable FetchDataTable(DataSourceCommand Command)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(this.ConvertToSQLCommand(Command));
            if (!this.IsConnected())
            {
                this.Open();
            }
            da.Fill(dt);
            this.Close();
            return dt;
        }

        public IDataReader FetchReader(DataSourceCommand Command)
        {
            return this.ConvertToSQLCommand(Command).ExecuteReader();
        }

        public bool IsConnected()
        {
            return (this._dbc.State == ConnectionState.Open);
        }

        public void Open()
        {
            if ((this._dbc.State != ConnectionState.Connecting) & (this._dbc.State != ConnectionState.Open))
            {
                this._dbc.Open();
            }
        }

        // Properties
        public string Provider
        {
            get
            {
                return "SqlClient";
            }
        }

        internal int MajorVersion
        {
            get
            {
                int value = 9;
                _dbc.Open();
                string[] args = _dbc.ServerVersion.Split('.');
                _dbc.Close();
                if (args.Length >= 1)
                {
                    if (!int.TryParse(args[0], out value)) { value = 9; }
                }
                return value;
            }
        }

        public string GetMetaData(string database, string owner, string table, string KeyName)
        {
            DataSourceCommand cmd = new DataSourceCommand(string.Format("USE {0};SELECT name, value FROM fn_listextendedproperty (NULL, 'schema', @schema, 'table', @tbl,null,null)", database));
            cmd.Parameters.Add("@schema", owner);
            cmd.Parameters.Add("@tbl", table);
            DataTable dt = FetchDataTable(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                if (dr["name"].ToString().ToLower() == KeyName.ToLower()) { if (dr["value"] == null) { return string.Empty; } else { return dr["value"].ToString(); } }
            }

            return string.Empty;
        }

        public string GetMetaData(string database, string owner, string table, string column, string KeyName)
        {
            DataSourceCommand cmd = new DataSourceCommand(string.Format("USE {0};SELECT name, value FROM fn_listextendedproperty (NULL, 'schema', @schema, 'table', @tbl,'column',null) WHERE objname = @column", database));
            cmd.Parameters.Add("@schema", owner);
            cmd.Parameters.Add("@column", column);
            cmd.Parameters.Add("@tbl", table);
            DataTable dt = FetchDataTable(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                if (dr["name"].ToString().ToLower() == KeyName.ToLower()) { if (dr["value"] == null) { return string.Empty; } else { return dr["value"].ToString(); } }
            }

            return string.Empty;
        }

        public string[] ListDatabases()
        {
            DataSourceCommand cmd = null;
            List<string> db = new List<string>();

            if (this.MajorVersion < 9)
            {
                cmd = new DataSourceCommand("SELECT name FROM master.dbo.sysdatabases ORDER BY name ASC");
            }
            else
            {
                cmd = new DataSourceCommand("SELECT name FROM sys.databases ORDER BY name ASC");
            }

            DataTable dt = this.FetchDataTable(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                db.Add(dr["name"].ToString());
            }

            return db.ToArray();
        }

        public DataSourceTable[] ListTables(string Database)
        {
            string Description = string.Empty;
            DataSourceCommand cmd = null;
            List<DataSourceTable> tbl = new List<DataSourceTable>();
            DataSourceTableType tbltype = DataSourceTableType.DataTable;
            if (this.MajorVersion < 9)
            {
                cmd = new DataSourceCommand(string.Format("SELECT a.type AS objtype, a.name, b.name AS owner FROM [{0}].dbo.sysobjects a INNER JOIN [{0}].dbo.sysusers b ON a.uid = b.uid WHERE a.type IN('U','V') ORDER BY a.name ASC", Database));
            }
            else
            {
                cmd = new DataSourceCommand(string.Format("SELECT a.type AS objtype, a.name, b.name AS owner FROM [{0}].sys.tables a INNER JOIN [{0}].sys.schemas b ON a.schema_id = b.schema_id WHERE a.type IN('U','V') ORDER BY a.name ASC", Database));
            }

            DataTable dt = this.FetchDataTable(cmd);
            foreach (DataRow dr in dt.Rows)
            {
                Description = GetMetaData(Database, dr["owner"].ToString(), dr["name"].ToString(), "MS_Description");
                if (Description == string.Empty) { Description = GetMetaData(Database, dr["owner"].ToString(), dr["name"].ToString(), "Description"); }

                switch (dr["objtype"].ToString().ToUpper())
                {
                    case "AF": break;//Aggregate function (CLR)
                    case "C": break;//CHECK constraint
                    case "D": break;//DEFAULT (constraint or stand-alone)
                    case "F": break;//FOREIGN KEY constraint
                    case "PK": break;//PRIMARY KEY constraint
                    case "P": break;//SQL stored procedure
                    case "PC": break;//Assembly (CLR) stored procedure
                    case "FN": break;//SQL scalar function
                    case "FS": break;//Assembly (CLR) scalar function
                    case "FT": tbltype = DataSourceTableType.ClrTableFunction; break;//Assembly (CLR) table-valued function
                    case "R": break;//Rule (old-style, stand-alone)
                    case "RF": break;//Replication-filter-procedure
                    case "S": tbltype = DataSourceTableType.SystemTable; break;//System base table
                    case "SN": break;//Synonym
                    case "SQ": break;//Service queue
                    case "TA": break;//Assembly (CLR) DML trigger
                    case "TR": break;//SQL DML trigger 
                    case "IF": tbltype = DataSourceTableType.InlineTableFunction; break;//SQL inline table-valued function
                    case "TF": tbltype = DataSourceTableType.TableFunction; break;//SQL table-valued-function
                    case "U": tbltype = DataSourceTableType.DataTable; break;//Table (user-defined)
                    case "UQ": break;//UNIQUE constraint
                    case "V": tbltype = DataSourceTableType.DataView; break;//View
                    case "X": break;//Extended stored procedure
                    case "IT": tbltype = DataSourceTableType.InternalTable; break;//Internal table
                }

                tbl.Add(new DataSourceTable(Database, dr["owner"].ToString(), dr["name"].ToString(), Description, tbltype));
            }

            return tbl.ToArray();
        }

        public DataSourceTableDefinition GetTableDefinition(DataSourceTable Table)
        {
            DataSourceCommand cmd = null;
            DataSourceCommand cmdidx = null;
            DataSourceTableDefinition tbl = new DataSourceTableDefinition(Table);
            string description = string.Empty;
            bool primarykey = false;
            bool uniquekey = false;
            int width = 0;
            int precision = 0;
            int scale = 0;

            if (this.MajorVersion < 9)
            {
                cmd = new DataSourceCommand(string.Format("SELECT COALESCE(e.text, '') AS defaultvalue, b.name, b.isnullable AS is_nullable, c.name AS data_type, COLUMNPROPERTY(b.id, b.name, 'charmaxlen') AS charwidth, CONVERT(tinyint, CASE WHEN b.xtype IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN b.xprec  END) AS numeric_precision, CONVERT(int, CASE WHEN b.xtype IN (40, 41, 42, 43, 58, 61) THEN NULL ELSE odbcscale(b.xtype, b.xscale) END) AS numeric_scale FROM {0}.dbo.sysobjects a INNER JOIN {0}.dbo.syscolumns b ON a.id = b.id LEFT JOIN {0}.dbo.systypes c ON b.xusertype = c.xusertype   INNER JOIN {0}.dbo.sysusers d ON a.uid = d.uid LEFT JOIN syscomments e ON b.cdefault = e.id WHERE a.name = @tbl AND  d.name = @owner AND a.type IN ('U', 'V') ORDER BY COLUMNPROPERTY(b.id, b.name, 'ordinal') ASC", Table.Database));
                cmdidx = new DataSourceCommand(string.Format("SELECT CAST (CASE WHEN ((a.status & 2048) > 0) THEN 1 ELSE 0 END AS bit) AS is_primary_key, CAST (CASE WHEN ((a.status & 4066) > 0) THEN 1 ELSE 0 END AS bit) AS is_unique, c.name FROM {0}.dbo.sysindexes a INNER JOIN {0}.dbo.sysindexkeys b ON a.indid = b.indid AND a.id = b.id INNER JOIN {0}.dbo.syscolumns c ON b.id = c.id AND b.colid = c.colid WHERE OBJECT_NAME(a.id) = @tbl AND (CAST (CASE WHEN ((a.status & 2048) > 0) THEN 1 ELSE 0 END AS bit) = 1 OR CAST (CASE WHEN ((a.status & 4066) > 0) THEN 1 ELSE 0 END AS bit) = 1)", Table.Database));
            }
            else
            {
                cmd = new DataSourceCommand(string.Format("SELECT COALESCE(e.definition, '') AS defaultvalue, b.name, b.is_nullable, c.name AS data_type, COLUMNPROPERTY(b.object_id, b.name, 'charmaxlen') AS charwidth, CONVERT(tinyint, CASE WHEN b.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN b.precision END) AS numeric_precision, CONVERT(int, CASE WHEN b.system_type_id IN (40, 41, 42, 43, 58, 61) THEN NULL ELSE odbcscale(b.system_type_id, b.scale) END) AS numeric_scale FROM  {0}.sys.objects a INNER JOIN {0}.sys.columns b ON a.object_id = b.object_id  LEFT JOIN {0}.sys.types c ON b.user_type_id = c.user_type_id  LEFT JOIN sys.default_constraints e ON b.default_object_id = e.object_id WHERE a.name = @tbl AND  SCHEMA_NAME(a.schema_id) = @owner AND a.type IN ('U', 'V')  ORDER BY COLUMNPROPERTY(b.object_id, b.name, 'ordinal') ASC", Table.Database));
                cmdidx = new DataSourceCommand(string.Format("SELECT a.is_primary_key, a.is_unique, c.Name FROM {0}.sys.indexes a INNER JOIN {0}.sys.index_columns b ON a.index_id = b.index_id AND a.object_id = b.object_id INNER JOIN {0}.sys.columns c ON b.column_id = c.column_id AND b.object_id = c.object_id WHERE OBJECT_NAME(a.OBJECT_ID) = @tbl AND (a.is_primary_key = 1 OR a.is_unique = 1)", Table.Database));
            }

            cmd.Parameters.Add("@tbl", Table.Name);
            cmd.Parameters.Add("@owner", Table.Owner);
            cmdidx.Parameters.Add("@tbl", Table.Name);
            DataTable dt = FetchDataTable(cmd);
            DataTable dtIdx = FetchDataTable(cmdidx);

            foreach (DataRow dr in dt.Rows)
            {
                primarykey = false;
                uniquekey = false;
                width = 0;
                precision = 0;
                scale = 0;
                description = string.Empty;
                DataSourceColumn col = null;

                if (dr["charwidth"] != DBNull.Value) { width = Convert.ToInt32(dr["charwidth"]); }
                if (dr["numeric_precision"] != DBNull.Value) { precision = Convert.ToInt32(dr["numeric_precision"]); }
                if (dr["numeric_scale"] != DBNull.Value) { scale = Convert.ToInt32(dr["numeric_scale"]); }

                description = GetMetaData(Table.Database, Table.Owner, Table.Name, dr["name"].ToString(), "MS_Description");
                if (description == string.Empty) { description = GetMetaData(Table.Database, Table.Owner, Table.Name, dr["name"].ToString(), "Description"); }

                foreach (DataRow drIdx in dtIdx.Rows)
                {
                    if (drIdx["name"].ToString() == dr["name"].ToString())
                    {
                        primarykey = Convert.ToBoolean(drIdx["is_primary_key"]);
                        uniquekey = Convert.ToBoolean(drIdx["is_unique"]);
                        break;
                    }
                }

                col = new DataSourceColumn((string)dr["name"], GetSqlDbType((string)dr["data_type"]), primarykey, uniquekey, Convert.ToBoolean(dr["is_nullable"]), width, precision, scale, description, dr["defaultvalue"].ToString().Trim());
                tbl.Add(col);
            }

            return tbl;
        }

    }
}
